﻿using System;
using System.Data;
using System.Data.Common;
using System.Data.Odbc;
using System.Data.OleDb;
using System.Data.SqlClient;

/*
* (C) Systementor AB Stefan Holmberg
 * MentorDB See Admentor.net
*/

namespace Jerkymon.Data
{


    /// <summary>
    /// Summary description for dbhelper.
    /// </summary>
    /// 
    public class DataLayerOLECreateClass
    {
		#region Static Methods (5)
		public static IDbConnection CreateConnection(string strConn, string strType)
        {
            strType = strType.ToUpper();
            if (strType == "ODBC")
                return new OdbcConnection(strConn);
            if (strType == "OLEDB")
                return new OleDbConnection(strConn);
            if (strType == "SQLSERVER")
                //return new SqlConnection(strConn);
                return new OleDbConnection(strConn);
            return null;
        }

		public static DbDataAdapter CreateDataAdapter(string strSQL, IDbConnection oConn)
        {
            string strType = GetType(oConn);
            strType = strType.ToUpper();
            if (strType == "ODBC")
                return new OdbcDataAdapter(strSQL, (OdbcConnection)oConn);
            if (strType == "OLEDB")
                return new OleDbDataAdapter(strSQL, (OleDbConnection)oConn);
            if (strType == "SQLSERVER")
                return new OleDbDataAdapter(strSQL, (OleDbConnection)oConn);
            //				return new SqlDataAdapter(strSQL, (SqlConnection)oConn);
            return null;
        }

		public static object CreateDateTimeParameter(IDbConnection oConn, string strName, DateTime dtValue)
        {
            string strType = GetType(oConn);
            strType = strType.ToUpper();
            if (strType == "ODBC")
            {
                OdbcParameter oPar = new OdbcParameter(strName, System.Data.Odbc.OdbcType.DateTime);
                oPar.Value = dtValue;
                return oPar;
            }
            if (strType == "OLEDB")
            {
                OleDbParameter oPar = new OleDbParameter(strName, System.Data.OleDb.OleDbType.DBDate);
                oPar.Value = dtValue;
                return oPar;
            }
            if (strType == "SQLSERVER")
            {
                OleDbParameter oPar = new OleDbParameter(strName, System.Data.OleDb.OleDbType.DBDate);
                oPar.Value = dtValue;
                return oPar;
                //			SqlParameter oPar = new SqlParameter(strName,System.Data.SqlDbType.DateTime);
                //				oPar.Value = dtValue;
                //				return oPar;
            }
            return null;
        }

		public static object CreateVarcharParameter(IDbConnection oConn, string strName, int nLen, string strValue)
        {
            string strType = GetType(oConn);
            strType = strType.ToUpper();
            if (strType == "ODBC")
            {
                OdbcParameter oPar = new OdbcParameter(strName, System.Data.Odbc.OdbcType.VarChar, nLen);
                oPar.Value = strValue;
                return oPar;
            }
            if (strType == "OLEDB")
            {
                OleDbParameter oPar = new OleDbParameter(strName, System.Data.OleDb.OleDbType.VarChar, nLen);
                oPar.Value = strValue;
                return oPar;
            }
            if (strType == "SQLSERVER")
            {
                OleDbParameter oPar = new OleDbParameter(strName, System.Data.OleDb.OleDbType.VarChar, nLen);
                oPar.Value = strValue;
                return oPar;
                //				SqlParameter oPar = new SqlParameter(strName,System.Data.SqlDbType.VarChar, nLen);
                //				oPar.Value = strValue;
                //				return oPar;
            }
            return null;
        }

		public static string GetType(IDbConnection oConn)
        {
            OleDbConnection Conn1;
            OdbcConnection Conn2;
            SqlConnection Conn3;
            try
            {
                Conn1 = (OleDbConnection)oConn;
                if (Conn1 != null)
                    return "OLEDB";
            }
            catch
            {
            }
            try
            {
                Conn2 = (OdbcConnection)oConn;
                if (Conn2 != null)
                    return "ODBC";
            }
            catch
            {
            }
            try
            {
                Conn3 = (SqlConnection)oConn;
                if (Conn3 != null)
                    return "SQLSERVER";
            }
            catch
            {
            }
            return "UNKNOWN";
        }
		#endregion
    };

    public class DataLayerOLEUpdater
    {
		#region Fields (8)
		private bool m_fNew = false;

		private DbDataAdapter m_oDA;

		private DataRow m_oDR;

		private DataSet m_oDs;

		private IDbConnection m_oConn = null;

		private long m_lNewID = -1;

		private string m_strSQL;
		private string m_strType;
		#endregion

		#region Properties (3)
		public bool IsNewRecord
        {
            get { return m_fNew; }
        }
		public long NewID
        {
            get { return m_lNewID; }
        }
		public DataRow Row
        {
            get { return m_oDR; }
        }
		#endregion

		#region Constructors (1)
		public DataLayerOLEUpdater(IDbConnection oConn, string strSQL)
        {
            m_oConn = oConn;
            m_strType = DataLayerOLECreateClass.GetType(oConn);
            m_strSQL = strSQL;
            Setup();
        }
		#endregion

		#region Methods (3)

		public bool Save()
        {
            if (m_strType == "OLEDB")
            {
                OleDbDataAdapter oAdapt = (OleDbDataAdapter)m_oDA;
                OleDbCommandBuilder builder = new OleDbCommandBuilder(oAdapt);
                oAdapt.UpdateCommand = builder.GetUpdateCommand();
                oAdapt.InsertCommand = builder.GetInsertCommand();
            }
            else if (m_strType == "ODBC")
            {
                OdbcDataAdapter oAdapt2 = (OdbcDataAdapter)m_oDA;
                OdbcCommandBuilder builder = new OdbcCommandBuilder(oAdapt2);
                oAdapt2.UpdateCommand = builder.GetUpdateCommand();
                oAdapt2.InsertCommand = builder.GetInsertCommand();
            }
            else if (m_strType == "SQLSERVER")
            {
                OleDbDataAdapter oAdapt = (OleDbDataAdapter)m_oDA;
                OleDbCommandBuilder builder = new OleDbCommandBuilder(oAdapt);
                oAdapt.UpdateCommand = builder.GetUpdateCommand();
                oAdapt.InsertCommand = builder.GetInsertCommand();
                //				SqlDataAdapter oAdapt3 = (SqlDataAdapter)m_oDA;
                //				SqlCommandBuilder builder = new SqlCommandBuilder(oAdapt3);
                //				oAdapt3.UpdateCommand = builder.GetUpdateCommand();
                //				oAdapt3.InsertCommand = builder.GetInsertCommand();
            }

            if (m_fNew)
                m_oDs.Tables["hej"].Rows.Add(m_oDR);

            m_oDA.Update(m_oDs, "hej");
            return true;
        }

		public bool SaveAndRetrieveID(string strAutoNumberFieldName)
        {
            if (m_strType == "OLEDB")
            {
                OleDbDataAdapter oAdapt = (OleDbDataAdapter)m_oDA;
                OleDbCommandBuilder builder = new OleDbCommandBuilder(oAdapt);
                oAdapt.UpdateCommand = builder.GetUpdateCommand();
                oAdapt.InsertCommand = builder.GetInsertCommand();
            }
            else if (m_strType == "ODBC")
            {
                OdbcDataAdapter oAdapt2 = (OdbcDataAdapter)m_oDA;
                OdbcCommandBuilder builder = new OdbcCommandBuilder(oAdapt2);
                oAdapt2.UpdateCommand = builder.GetUpdateCommand();
                oAdapt2.InsertCommand = builder.GetInsertCommand();
            }
            else if (m_strType == "SQLSERVER")
            {
                OleDbDataAdapter oAdapt = (OleDbDataAdapter)m_oDA;
                OleDbCommandBuilder builder = new OleDbCommandBuilder(oAdapt);
                oAdapt.UpdateCommand = builder.GetUpdateCommand();
                oAdapt.InsertCommand = builder.GetInsertCommand();
                //				SqlDataAdapter oAdapt3 = (SqlDataAdapter)m_oDA;
                //				SqlCommandBuilder builder = new SqlCommandBuilder(oAdapt3);
                //				oAdapt3.UpdateCommand = builder.GetUpdateCommand();
                //				oAdapt3.InsertCommand = builder.GetInsertCommand();
            }

            IdentityHook helper = new IdentityHook(m_oDA, strAutoNumberFieldName);
            if (m_fNew)
                m_oDs.Tables["hej"].Rows.Add(m_oDR);

            m_oDA.Update(m_oDs, "hej");
            m_lNewID = helper.m_nNewID;
            return true;
        }

		private void Setup()
        {
            if (m_strType == "OLEDB")
            {
                m_oDA = new OleDbDataAdapter(m_strSQL, (OleDbConnection)m_oConn);
            }
            else if (m_strType == "ODBC")
            {
                m_oDA = new OdbcDataAdapter(m_strSQL, (OdbcConnection)m_oConn);
            }
            else if (m_strType == "SQLSERVER")
            {
                m_oDA = new OleDbDataAdapter(m_strSQL, (OleDbConnection)m_oConn);
                //m_oDA = new SqlDataAdapter(m_strSQL,(SqlConnection)m_oConn); 
            }
            m_oDs = new DataSet();
            m_oDA.Fill(m_oDs, "hej");
            if (m_oDs.Tables["hej"].Rows.Count == 0)
            {
                m_oDR = m_oDs.Tables["hej"].NewRow();
                m_fNew = true;
            }
            else
                m_oDR = m_oDs.Tables["hej"].Rows[0];
        }

		#endregion
    }

    public class DataLayerOLEExecutor
    {
		#region Static Methods (5)
		public static DataSet GetDataSet(IDbConnection oConn, string strSQL, string strTableName)
        {
            DataSet oDataSet = new DataSet(strTableName);

            DbDataAdapter oAdapter = DataLayerOLECreateClass.CreateDataAdapter(strSQL, oConn);
            oAdapter.Fill(oDataSet, strTableName);
            return oDataSet;
        }

		public static long GetScalar(IDbConnection Conn, string strSQL)
        {
            IDbCommand oCommand = Conn.CreateCommand();
            oCommand.CommandText = strSQL;
            long lCount = Convert.ToInt64(oCommand.ExecuteScalar());
            return lCount;
        }

		public static DataTable GetTable(IDbConnection oConn, string strSQL)
        {
            return GetDataSet(oConn, strSQL, "table1").Tables["table1"];
        }

		public static DataTable GetTable1VarCharParam(IDbConnection Conn, string strSQL, string strValue, int nColumnLen)
        {
            DataSet oDataSet = new System.Data.DataSet("temp");
            IDbCommand oCommand = Conn.CreateCommand();
            oCommand.CommandText = strSQL;
            oCommand.Parameters.Add(DataLayerOLECreateClass.CreateVarcharParameter(Conn, "@param1", nColumnLen, strValue));
            DbDataAdapter oAdapter = DataLayerOLECreateClass.CreateDataAdapter("", Conn);
            IDbDataAdapter iAdapt = (IDbDataAdapter)oAdapter;
            iAdapt.SelectCommand = oCommand;
            oAdapter.Fill(oDataSet, "temp");
            return oDataSet.Tables["temp"];
        }

		public static void RunCommand(IDbConnection Conn, string strSQL)
        {
            IDbCommand oCommand = Conn.CreateCommand();
            oCommand.CommandTimeout = 3600;
            oCommand.CommandText = strSQL;
            oCommand.ExecuteNonQuery();
        }
		#endregion
    }

    /// <summary>
    /// Summary description for IdentityHook
    /// </summary>
    public class IdentityHook
    {
		#region Fields (2)
		public int m_nNewID;

		private string m_strAutoNumberFieldName;
		#endregion

		#region Constructors (1)
		public IdentityHook(DbDataAdapter oAdapter, string strAutoNumberFieldName)
        {
            IDbDataAdapter iAdapt = (IDbDataAdapter)oAdapter;
            string strType = DataLayerOLECreateClass.GetType(iAdapt.InsertCommand.Connection);
            if (strType == "OLEDB")
            {
                OleDbDataAdapter dDat = (OleDbDataAdapter)oAdapter;
                dDat.RowUpdated += new OleDbRowUpdatedEventHandler(RowUpdatedOledb);
            }
            else
            {
                OdbcDataAdapter dDat2 = (OdbcDataAdapter)oAdapter;
                dDat2.RowUpdated += new OdbcRowUpdatedEventHandler(RowUpdatedOdbc);
            }
            m_strAutoNumberFieldName = strAutoNumberFieldName;
            m_nNewID = -1;
        }
		#endregion

		#region Methods (2)

		protected void RowUpdatedOdbc(object sender, OdbcRowUpdatedEventArgs args)
        {
            OdbcDataAdapter oHook = (OdbcDataAdapter)sender;
            int newID = 0;

            if (args.StatementType == StatementType.Insert)
            {
                OdbcCommand idCMD = new OdbcCommand("SELECT @@IDENTITY", oHook.InsertCommand.Connection);
                try
                {
                    newID = Convert.ToInt32(idCMD.ExecuteScalar());
                }
                catch
                {

                }
                idCMD.Dispose();
                args.Row[m_strAutoNumberFieldName] = newID;
                m_nNewID = newID;
            }
        }

		protected void RowUpdatedOledb(object sender, OleDbRowUpdatedEventArgs args)
        {
            OleDbDataAdapter oHook = (OleDbDataAdapter)sender;
            int newID = 0;

            if (args.StatementType == StatementType.Insert)
            {
                OleDbCommand idCMD = new OleDbCommand("SELECT @@IDENTITY", oHook.InsertCommand.Connection);
                try
                {
                    newID = Convert.ToInt32(idCMD.ExecuteScalar());
                }
                catch
                {

                }
                idCMD.Dispose();
                args.Row[m_strAutoNumberFieldName] = newID;
                m_nNewID = newID;
            }
        }

		#endregion
    }
}
